package com.py.longtrans.excel;


import com.py.longtrans.LongTrans;
import com.py.longtrans.entity.InterfaceEntity;
import com.py.longtrans.entity.ServiceEntity;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import sun.misc.BASE64Decoder;

import java.io.*;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.Date;

public class ExcelUtil {

    private static Logger logger = LoggerFactory.getLogger(ExcelUtil.class);
//    Logger logger = LogManager.getLogger("RollingRandomAccessFileLogger");

    private static HashMap<String,Double> getInterfaceSum() throws IOException {
        ResourceBundle resource = ResourceBundle.getBundle("path");
        SimpleDateFormat mf = new SimpleDateFormat("yyyy-MM-dd");
        Date date = new Date();
        Calendar calendar = Calendar.getInstance();
        calendar.setTime(date);
//        calendar.set(Calendar.MONTH, calendar.get(Calendar.MONTH) - 1); // 设置为上一个月
        calendar.set(Calendar.DAY_OF_MONTH,calendar.get(Calendar.DAY_OF_MONTH)-1); // 设置为前一天
        date = calendar.getTime();
        String accDate = mf.format(date);
        String anPath = resource.getString("path.everyInterface");
        String path = anPath+accDate+"_analysis.xlsx";
        InterfaceEntity entity = new InterfaceEntity();
        HashMap<String,Double> map = new HashMap<String,Double>();
        //1、获取文件输入流
        InputStream inputStream = new FileInputStream(path);
        //2、获取Excel工作簿对象
        XSSFWorkbook workbook = new XSSFWorkbook(inputStream);

        //3、得到Excel工作表对象
        XSSFSheet sheetAt = workbook.getSheet("按分钟统计的每个接口调用量");
        //4、获取最后一行数据
        Row frontRow = sheetAt.getRow(0);
        Row lastRow = sheetAt.getRow(sheetAt.getLastRowNum());
        for (int i=1;i<frontRow.getLastCellNum();i++){
            String s = frontRow.getCell(i).getStringCellValue();
            double d = lastRow.getCell(i).getNumericCellValue(); //730624
            map.put(s,d);
        }
        inputStream.close();
        return map;
    }


//    public static void main(String[] args) throws IOException, SQLException, SftpException {
    public static void ProcessIn() throws IOException, SQLException{
        ResourceBundle resource = ResourceBundle.getBundle("path");
        String url = resource.getString("database.url");
        String id = resource.getString("database.id");
        String basePassword = resource.getString("database.password");
        String password = null;
        String pythonUrl = resource.getString("pythonDatabase.url");
        String pythonId = resource.getString("pythonDatabase.id");
        String pythonPassword = resource.getString("pythonDatabase.password");
        final BASE64Decoder decoder = new BASE64Decoder();
        try {
            password = new String(decoder.decodeBuffer(basePassword), "UTF-8");
        } catch (IOException e) {
            logger.error(e.toString());
        }
//        System.out.println("密码(后续删除):"+password);
//        SFTPUtil sftpUtil = new SFTPUtil();
        PreparedStatement pstm = null;
        PreparedStatement pythonPstm = null;
        ResultSet rs = null;
        ResultSet pythonRs = null;
        HashMap<String,Double> map = getInterfaceSum();
        HashMap<String,Double>  map1 = new HashMap();
        logger.info("加载驱动，获取数据库连接");
        Connection conn = DriverManager.getConnection(url, id, password);
        Connection pythonConn = DriverManager.getConnection(pythonUrl,pythonId,pythonPassword);
//        System.out.println("连接成功");
        logger.info("连接成功");
        SimpleDateFormat mf = new SimpleDateFormat("yyyy-MM-dd 00:00:00");
        Date date = new Date();
        Date currentDate = new Date();
        Calendar calendar = Calendar.getInstance();
        Calendar currentCalendar = Calendar.getInstance();
        calendar.setTime(date);
//        calendar.set(Calendar.MONTH, calendar.get(Calendar.MONTH) - 1); // 设置为上一个月
        calendar.set(Calendar.DAY_OF_MONTH,calendar.get(Calendar.DAY_OF_MONTH)-1); // 前一天日期
        date = calendar.getTime();
        currentDate = currentCalendar.getTime();
        String currentDay = mf.format(currentDate);
        String accDate = mf.format(date);
        String firstSql = resource.getString("database.sql");
        String sql = firstSql + " where creation_date<to_date('" +currentDay + "','yyyy-mm-dd hh24:mi:ss') and creation_date>to_date('" + accDate + "','yyyy-mm-dd hh24:mi:ss') group by log_type";
        String pythonSql = "select interface, sum(avg_dur)/count(interface) avg_dur, sum(concurrency) concurrency from wfp_statistics_interface_ca where gen_date = '"+accDate.split(" ")[0]+"' group by interface";
//        select count(1) as sum,log_type from WFP_INTERFACE_LOG group by log_type
        logger.info("准备执行sql");
        pstm = conn.prepareStatement(sql);
        pythonPstm = pythonConn.prepareStatement(pythonSql);
        logger.info("执行query操作");
        rs = pstm.executeQuery();
        pythonRs = pythonPstm.executeQuery();
        while (rs.next()) {
            Double sum = Double.valueOf(rs.getString("sum"));
            String log_type = rs.getString("log_type");
            if(map.containsKey(log_type)) {
                map1.put(log_type,(map.get(log_type) - sum));
//                logger.info("接口名称:"+log_type);
//                logger.info("调用成功量：" + (map.get(log_type) - sum));
            }
        }

        Map<String,List<Object>> pythonMap = new HashMap<>();
        while(pythonRs.next()){
            List<Object> list = new ArrayList<>();
            String interfaceName = pythonRs.getString("interface");
            Double avg_dur = Double.valueOf(pythonRs.getString("avg_dur"));
            Integer concurrency = Integer.valueOf(pythonRs.getString("concurrency"))/60;
            if (concurrency==0)
                concurrency=1;
            list.add(avg_dur);
            list.add(concurrency);
            pythonMap.put(interfaceName,list);
        }
        InterfaceEntity entity = new InterfaceEntity();
//        ExcelOut(entity,map1,map);
        TxtOut(entity,map1,map,pythonMap);
//        sftpUtil.uploadExl();
        String s1 = accDate.replaceAll("-","");
        String filePath = resource.getString("path.out");
        String chkFilePath = filePath+s1.replaceAll("-","")+"_00.chk";
        filePath = filePath+s1.replaceAll("-","")+"_00.txt";
        LongTrans.transFile(chkFilePath);
        LongTrans.transFile(filePath);
//        in.close();
    }


    private static void TxtOut(InterfaceEntity entity, HashMap<String,Double> map,HashMap<String,Double> sumMap,Map<String,List<Object>> pythonMap) {
        logger.info("日传及月传-txt-输出");
        ResourceBundle resource = ResourceBundle.getBundle("path");
        String path = resource.getString("path.out");
        String monthPath = resource.getString("path.monthout");
        ServiceEntity serviceEntity = new ServiceEntity();

        SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");//设置日期格式
        SimpleDateFormat mf = new SimpleDateFormat("yyyy-MM-dd");
        Date date = new Date();
        Calendar calendar = Calendar.getInstance();
        calendar.setTime(date);
        calendar.set(Calendar.DAY_OF_MONTH,calendar.get(Calendar.DAY_OF_MONTH)-1); // 前一天日期
        date = calendar.getTime();
        String accDate = mf.format(date);
        String s1 = accDate.replaceAll("-","");
        String currentTime = df.format(new Date());// new Date()为获取当前系统时间
//        String s1 = currentTime.split(" ")[0]; // 2022-02-23
        String s2 = accDate.split("-")[0]+accDate.split("-")[1];
        //新业务逻辑
        //D_006_F10004_006_68903_20220223_00.txt

        String filepath = path+s1.replaceAll("-","")+"_00.txt";
        String chkpath = path+s1.replaceAll("-","")+"_00.chk";
        String monthTxtPath = monthPath+s2+"_00.txt";
        String monthChkPath = monthPath+s2+"_00.chk";
        File file = new File(filepath);
        File chkFile = new File(chkpath);
        File monthTxtFile = new File(monthTxtPath);
        File monthChkFile = new File(monthChkPath);
        try {
            if (!monthTxtFile.exists())
                monthTxtFile.createNewFile();
            if (!monthChkFile.exists())
                monthChkFile.createNewFile();
            file.createNewFile();
            chkFile.createNewFile();
            BufferedWriter writer = new BufferedWriter(new FileWriter(filepath, true));
            BufferedWriter monthWriter = new BufferedWriter(new FileWriter(monthTxtFile, true));
            BufferedWriter chkWriter = new BufferedWriter(new FileWriter(chkpath, true));
            BufferedWriter monthChkWriter = new BufferedWriter(new FileWriter(monthChkFile, true));
            for (int i = 0; i < 42; i++) {
                StringBuffer buffer = new StringBuffer(s1.replaceAll("-", ""));
                buffer.append("€€OSB_BP_WFP_HQ_000" + (i + 38)).append("€€");
                buffer.append("€€集中化流程引擎€€A100600080€€M域集中化系统建设支撑€€API类€€无€€");
                buffer.append(ServiceEntity.list.get(i)).append("€€");
                buffer.append("OSB_BP_WFP_HQ_000" + (i + 38)).append("€€");
                buffer.append("集中化流程平台€€A100600080€€M域集中化系统€€无€€M域集中化系统€€无€€内部€€");
                String interName = InterfaceEntity.map.get(ServiceEntity.list.get(i));
                if ((map.get(interName)) != null) {
                    buffer.append(sumMap.get(interName)).append("€€");
                    buffer.append(map.get(interName)).append("€€");
                } else {
                    buffer.append(sumMap.get(interName)).append("€€");
                    buffer.append(sumMap.get(interName)).append("€€");
                }
                if ((pythonMap.get(interName))!=null){
                    buffer.append(pythonMap.get(interName).get(0)).append("€€");
                    buffer.append(pythonMap.get(interName).get(1)).append("€€");
                }else {
                    buffer.append(0).append("€€").append(0).append("€€");
                }
                writer.write(buffer.toString() + "\r\n");
                chkWriter.write(buffer.toString() + "\r\n");
                monthWriter.write(buffer.toString() + "\r\n");
                monthChkWriter.write(buffer.toString() + "\r\n");
            }
            writer.flush();
            writer.close();
            chkWriter.flush();
            chkWriter.close();
            monthWriter.flush();
            monthWriter.close();
            monthChkWriter.flush();
            monthChkWriter.close();
        } catch (IOException e) {
            logger.error(e.toString());
        }
    }

}
